﻿/******************************************************   
创建者: ljj
创建时间: 2017-07-31
修改:
修改时间: 
功能: 调拨方案
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using NHibernateVertica;
using com.nm.library.Helper;
using com.nm.library;
using CommodityTransferAPI.Model;
using com.nm.Attributes;
using Nancy.Extensions;
using BaseAPI;
using george.Helper;
using Vertica.Data.VerticaClient;

namespace CommodityTransferAPI
{
    [Description("调拨方案")]
    public class MvAllotProjectHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region GetProjectList 调拨方案列表

        /// Writer：ljj
        /// Create Date：2017-07-31
        [MustLogin]
        [Description("调拨方案列表")]
        [ReturnType(typeof(IList<MvAllotProjectMasterModel.CoreModel>))]
        public ApiResult GetProjectList()
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var list = ReturnProjectList();
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region ReturnProjectList 返回调拨方案列表

        /// <summary>
        /// 返回调拨方案列表
        /// </summary>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2017-07-31
        public IList<MvAllotProjectMasterModel.CoreModel> ReturnProjectList()
        {
            try
            {
                //用户ID
                var uid = Convert.ToInt32(UserInfo.UserID);
                var companyId = UserInfo.UserCompanyId;
                var list = new OperationAreaUserHandler().GetOperationList(uid);
                if (list.Count==1)//单分布
                {
                   var sql1 = $@"
SELECT m.ExtrSource , m.CompanyID , m.ProjectCode , m.ProjectName,m.ProjectState
FROM ngoods.ngoods_Mv_AllotProject_Master m
WHERE m.CompanyID = '{companyId}' and m.AllotArea = 1 and
m.ProjectCode in 
(
    select ProjectCode from ngoods.ngoods_Mv_AllotProject_Detail d 
    where d.OperationAreaID = '{list[0]}' and d.CompanyID = '{companyId}'
)
ORDER BY m.AllotArea,m.ProjectCode";
                    var dt1 = Helper.ExecuteDataTable(new VerticaCommand(sql1));
                    var tList = ModelConvertHelper<MvAllotProjectMasterModel.CoreModel>.ConvertToModel(dt1);
                    return tList;
                }
                else if (list.Count > 1 && list.Count < 10)//多分部
                {
                    var sql2 = $@"SELECT m.ExtrSource , m.CompanyID , m.ProjectCode , m.ProjectName,m.ProjectState
FROM ngoods.ngoods_Mv_AllotProject_Master m
WHERE m.CompanyID = '{companyId}' and (m.AllotArea = 2 or m.AllotArea = 1 )  and 
m.ProjectCode in 
(
    select ProjectCode from ngoods.ngoods_Mv_AllotProject_Detail d 
    where d.OperationAreaID in('{list.ToArray().Join("','")}') and d.CompanyID = '{companyId}'
)
ORDER BY m.AllotArea,m.ProjectCode";

                    var dt2 = Helper.ExecuteDataTable(new VerticaCommand(sql2));
                    var tList = ModelConvertHelper<MvAllotProjectMasterModel.CoreModel>.ConvertToModel(dt2);
                    return tList;
                }
                else if (list.Count == 10)//组合
                {
                  var  sql3 = $@"SELECT m.ExtrSource , m.CompanyID , m.ProjectCode , m.ProjectName,m.ProjectState
FROM ngoods.ngoods_Mv_AllotProject_Master m
WHERE  m.CompanyID = '{companyId}' 
order by m.AllotArea ,m.ProjectCode";
                   var dt3 = Helper.ExecuteDataTable(new VerticaCommand(sql3));
                    var tList = ModelConvertHelper<MvAllotProjectMasterModel.CoreModel>.ConvertToModel(dt3);
                    return tList;
                }
                else
                {
                    throw (new Exception("当前用户未设置零售分部权限！"));
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region GetProject 调拨方案

        /// Writer：ljj
        /// Create Date：2017-07-31
        [MustLogin]
        [Description("调拨方案")]
        [ReturnType(typeof(MvAllotProjectMasterModel.ShowModel))]
        public ApiResult GetProject([ParamDescription("方案编码")] string ProjectCode = "")
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (string.IsNullOrEmpty(ProjectCode))
                {
                    return Error("没有方案编码!");
                }
                var list = ReturnProject(ProjectCode);
                var result = list.Select(d => new MvAllotProjectMasterModel.ShowModel
                {
                    ExtrSource = d.ExtrSource,
                    CompanyID = d.CompanyID,
                    AllotArea = d.AllotArea,
                    CreateDate = d.CreateDate,
                    Creator = d.Creator,
                    ExecCycle = d.ExecCycle,
                    ExecWeek = d.ExecWeek,
                    PriorityOperationArea = d.PriorityOperationArea,
                    ProjectCode = d.ProjectCode,
                    ProjectName = d.ProjectName,
                    ProjectState = d.ProjectState
                }).First();
                result.MaterialList = list.Select(d => new MvAllotProjectDetailModel.MaterialModel
                {
                    MaterialID = d.MaterialID,
                    MaterialShortName = d.MaterialShortName
                }).ToList();
                result.OperationAreaList = list.Select(d => d.OperationAreaID).Distinct().ToList();
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region ReturnProject 调拨方案

        /// <summary>
        /// 调拨方案
        /// </summary>
        /// <param name="ProjectCode"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2017-07-31
        public IList<MvAllotProjectMasterModel.ListModel> ReturnProject(string ProjectCode)
        {
            try
            {
                var sql = $@"SELECT  master.* ,
        detail.OperationAreaID ,
        detail.MaterialID ,
        material.MaterialShortName 
FROM    ngoods.ngoods_Mv_AllotProject_Master master
        INNER JOIN ( SELECT *
                     FROM   ngoods.ngoods_Mv_AllotProject_Detail
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                            AND ProjectCode = '{ProjectCode}'
                   ) detail ON master.ExtrSource = detail.ExtrSource
                               AND master.CompanyID = detail.CompanyID
                               AND master.ProjectCode = detail.ProjectCode
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Material2
                     WHERE  ExtrSource = 'KM'
                            AND CompanyID = 'KM'
                   ) material ON master.ExtrSource = material.ExtrSource
                                 AND master.CompanyID = material.CompanyID
                                 AND detail.MaterialID = material.MaterialID
WHERE   master.ExtrSource = '{UserInfo.UserExtrSource}'
        AND master.CompanyID = '{UserInfo.UserCompanyId}'
        AND master.ProjectCode = '{ProjectCode}'";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<MvAllotProjectMasterModel.ListModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region SaveProject 保存调拨方案

        /// Writer：ljj
        /// Create Date：2017-07-31
        [JsonType]
        [MustLogin]
        [Description("保存调拨方案")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult SaveProject([ParamDescription("方案")] MvAllotProjectMasterModel.ShowModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (param == null)
                {
                    return Error("参数对象为空!");
                }
                if (param.AllotArea == null || (param.AllotArea.Value != 1 && param.AllotArea.Value != 2 && param.AllotArea.Value != 3))
                {
                    return Error("无效的调拨区域!");
                }
                if (param.ProjectState == null)
                {
                    return Error("方案状态参数错误!");
                }
                if (param.ExecCycle == null || (param.ExecCycle.Value != 1 && param.ExecCycle.Value != 2 && param.ExecCycle.Value != 3))
                {
                    return Error("无效的执行周期!");
                }
                if (param.ExecWeek == null || (param.ExecWeek.Value < 1 || param.ExecWeek.Value > 7))
                {
                    return Error("无效的执行时间!");
                }
                if (string.IsNullOrWhiteSpace(param.PriorityOperationArea))
                {
                    return Error("分部优先不能为空!");
                }

                var allotAreaList = ReturnAllotArea();
                var result = string.IsNullOrEmpty(param.ProjectCode) ? CreateProject(param, allotAreaList) : ModifyProject(param, allotAreaList);
                return result == "0" ? Success(0) : Error(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region CreateProject 创建调拨方案

        /// Writer：ljj
        /// Create Date：2017-07-31
        [Description("创建调拨方案")]
        public string CreateProject(MvAllotProjectMasterModel.ShowModel param, IList<MvAllotProjectMasterModel.ListModel> AllotAreaList)
        {
            try
            {
                var sql = $@"INSERT INTO ngoods.ngoods_Mv_AllotProject_Master
(ExtrSource, CompanyID, AllotArea, ProjectCode, ProjectName, ProjectState, ExecCycle, ExecWeek, PriorityOperationArea, CreateDate, Creator)
VALUES('{UserInfo.UserExtrSource}', '{UserInfo.UserCompanyId}', {{0}}, '{{1}}', '{{2}}', '{{3}}', {{4}}, {{5}}, '{{6}}', '{DateTime.Now}', {UserInfo.UserID});";
                var uid = Guid.NewGuid().ToString("N");
                if (AllotAreaList.Any(d => d.ProjectCode == uid)) return "调拨方案编号已存在";
                if (AllotAreaList.Any(d => d.ProjectName == param.ProjectName && d.ProjectCode != uid))
                    return "调拨方案名称已存在";
                foreach (var item in param.OperationAreaList)
                {
                    if (AllotAreaList.Where(d => d.AllotArea == param.AllotArea.Value)
                        .Any(d => d.OperationAreaID == item))
                        return $"零售分部已被设置";
                }
                var sb = string.Format(sql, param.AllotArea, uid, param.ProjectName, param.ProjectState, param.ExecCycle, param.ExecWeek, param.AllotArea == 2 ? param.PriorityOperationArea : null);
                var detailList = new List<MvAllotProjectDetailModel.CreateModel>();
                foreach (var o in param.OperationAreaList)
                {
                    foreach (var m in param.MaterialList)
                    {
                        var detail = new MvAllotProjectDetailModel.CreateModel
                        {
                            ExtrSource = UserInfo.UserExtrSource,
                            CompanyID = UserInfo.UserCompanyId,
                            ProjectCode = uid,
                            OperationAreaID = o,
                            MaterialID = m.MaterialID,
                        };
                        detailList.Add(detail);
                    }
                }
                var dt = DataTableHelper.LinqToDataTable(detailList);
                Helper.ExecuteSqlTran(dt, "ngoods.ngoods_Mv_AllotProject_Detail", sb);
                return "0";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        #endregion

        #region ModifyProject 修改调拨方案

        /// Writer：ljj
        /// Create Date：2017-07-31
        [Description("修改调拨方案")]
        public string ModifyProject(MvAllotProjectMasterModel.ShowModel param, IList<MvAllotProjectMasterModel.ListModel> AllotAreaList)
        {
            try
            {
                var sqlUpdate = $@"UPDATE ngoods.ngoods_Mv_AllotProject_Master
SET AllotArea={{0}}, ProjectName='{{1}}', ProjectState='{{2}}', ExecCycle={{3}}, ExecWeek={{4}}, PriorityOperationArea='{{5}}', CreateDate='{DateTime.Now}', Creator={UserInfo.UserID}
WHERE ExtrSource='{UserInfo.UserExtrSource}' AND CompanyID='{UserInfo.UserCompanyId}' AND ProjectCode='{{6}}'; ";
                var sqlDel = $@"DELETE FROM  ngoods.ngoods_Mv_AllotProject_Detail
WHERE ExtrSource='{UserInfo.UserExtrSource}' AND CompanyID='{UserInfo.UserCompanyId}' AND ProjectCode='{{0}}'";
                if (AllotAreaList.Any(d => d.ProjectName == param.ProjectName && d.ProjectCode != param.ProjectCode))
                    return "调拨方案名称已存在";
                foreach (var item in param.OperationAreaList)
                {
                    if (AllotAreaList.Where(d => d.AllotArea == param.AllotArea.Value)
                        .Any(d => d.OperationAreaID == item && d.ProjectCode != param.ProjectCode))
                        return $"零售分部已被设置";
                }
                var sb = new StringBuilder();
                sb.AppendLine(string.Format(sqlUpdate, param.AllotArea, param.ProjectName, param.ProjectState,
                    param.ExecCycle, param.ExecWeek, param.AllotArea == 2 ? param.PriorityOperationArea : null,
                    param.ProjectCode));
                sb.AppendLine(string.Format(sqlDel, param.ProjectCode));
                var detailList = new List<MvAllotProjectDetailModel.CreateModel>();
                foreach (var o in param.OperationAreaList)
                {
                    foreach (var m in param.MaterialList)
                    {
                        var detail = new MvAllotProjectDetailModel.CreateModel
                        {
                            ExtrSource = UserInfo.UserExtrSource,
                            CompanyID = UserInfo.UserCompanyId,
                            ProjectCode = param.ProjectCode,
                            OperationAreaID = o,
                            MaterialID = m.MaterialID,
                        };
                        detailList.Add(detail);
                    }
                }
                var dt = DataTableHelper.LinqToDataTable(detailList);
                Helper.ExecuteSqlTran(dt, "ngoods.ngoods_Mv_AllotProject_Detail", sb.ToString());
                return "0";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        #endregion

        #region ReturnAllotArea 

        /// <summary>
        /// 
        /// </summary>
        /// <param name="AllotArea"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2017-07-31
        public IList<MvAllotProjectMasterModel.ListModel> ReturnAllotArea()
        {
            try
            {
                var sql = $@"SELECT  master.ProjectCode ,
        master.ProjectName ,
        detail.OperationAreaID ,
        master.AllotArea
FROM    ngoods.ngoods_Mv_AllotProject_Master master
        INNER JOIN ( SELECT *
                     FROM   ngoods.ngoods_Mv_AllotProject_Detail
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) detail ON master.ExtrSource = detail.ExtrSource
                               AND master.CompanyID = detail.CompanyID
                               AND master.ProjectCode = detail.ProjectCode
WHERE   master.ExtrSource = '{UserInfo.UserExtrSource}'
        AND master.CompanyID = '{UserInfo.UserCompanyId}'
GROUP BY master.ProjectCode ,
        master.ProjectName ,
        detail.OperationAreaID ,
        master.AllotArea";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<MvAllotProjectMasterModel.ListModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region GetUsedOperationArea 已用调拨区域

        /// Writer：ljj
        /// Create Date：2017-08-10
        [MustLogin]
        [Description("已用调拨区域")]
        [ReturnType(typeof(IList<MvAllotProjectDetailModel.CoreModel>))]
        public ApiResult GetUsedOperationArea([ParamDescription("调拨区域")] long AllotArea = -1)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var list = ReturnAllotArea();
                var oaList = list.Where(d => d.AllotArea == AllotArea).Select(
                    d => new MvAllotProjectDetailModel.CoreModel
                    {
                        OperationAreaID = d.OperationAreaID
                    }).DistinctBy(d => d.OperationAreaID).ToList();
                return Success(oaList);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion


    }
}
